package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;

import misc.Session;
import bean.Article;
import bean.Customer;
import bean.Employee;
import bean.Order;
import exceptions.IdNotFoundException;
import exceptions.NoDBConnException;

public class DB_Handler {
	private static DB_Handler instance = null;
	
	private Connection db;
	
	private DB_Handler() {
	}
	
	private Connection getConnection() throws NoDBConnException {
		try {
			return (this.db = DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/versandshop",
				Session.username,
				Session.pw));
		} catch(SQLException ex) {
			System.out.print("Fehler beim Verbindungsaufbau: " + ex.getMessage());
			throw new NoDBConnException("Fehler beim Verbindungsaufbau");
		}
	}
	
	private void closeConnection() {
		try {
			if(this.db != null)
				this.db.close();
		} catch (SQLException e) {
			System.err.println("Error while closing connection");
		}
	}
	
	public boolean testConnection(){
		boolean success;
		try{
			this.getConnection();
			success = true;
		} catch(NoDBConnException e){
			success = false;
		} finally{
			this.closeConnection();
		}
		return success;
	}
	public static DB_Handler getInstance(){
		if(DB_Handler.instance == null)
			DB_Handler.instance = new DB_Handler();
		return DB_Handler.instance;
	}
	/*
	public Article getArticle(int article_id){}
	public ArrayList<Article> getAllArticle() {}
	public ArrayList<WarehouseArticle> getAllWarehouseArticle() {}
	public ArrayList<DownloadArticle> getAllDownloadArticle() {}
	
	public void updateArticle(Article article) {}
	public boolean articleExists(Article article) {}
	
	*/
	public Article addArticle(Article article) {
		try {
			article = new DB_Article(this.getConnection()).add(article);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return article;
	}
	
	public ArrayList<Article> getAllArticles(){
		ArrayList<Article> articles = new ArrayList<Article>();
		try {
			articles = new DB_Article(this.getConnection()).getAll();
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return articles;
	}
		//
	public Article getArticle(int article_id) throws IdNotFoundException {
		Article article = null;
		try {
			article = new DB_Article(this.getConnection()).getById(article_id);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return article;
	}

	public void updateArticle(Article article) {
		try {
			new DB_Article(this.getConnection()).update(article);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
	}
	
	//
	public Customer getCustomer(int customer_id) throws IdNotFoundException {
		Customer customer = null;
		try {
			customer = new DB_Customer(this.getConnection()).getById(customer_id);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return customer;
	}
	public ArrayList<Customer> getAllCustomer(){
		ArrayList<Customer> customers = new ArrayList<Customer>();
		try {
			customers = new DB_Customer(this.getConnection()).getAll();
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return customers;
	}
	public Customer addCustomer(Customer customer){
		try {
			customer = new DB_Customer(this.getConnection()).add(customer);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return customer;
	}
	public void updateCustomer(Customer customer) {
		try {
			new DB_Customer(this.getConnection()).update(customer);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
	}
	
	public Employee getEmployee(int employee_id) throws IdNotFoundException {
		Employee employee = null;
		try {
			employee = new DB_Employee(this.getConnection()).getById(employee_id);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return employee;
	}
	public ArrayList<Employee> getAllEmployee() {
		ArrayList<Employee> employee = new ArrayList<Employee>();
		try {
			employee = new DB_Employee(this.getConnection()).getAll();
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return employee;
	}
	public Employee addEmployee(Employee employee){
		try {
			employee = new DB_Employee(this.getConnection()).add(employee);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return employee;
	}
	public void updateEmployee(Employee employee) {
		try {
			new DB_Employee(this.getConnection()).update(employee);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
	}
	
	public Order addOrder(Order order){
		try {
			order = new DB_Order(getConnection()).add(order);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return order;
	}
	
	public ArrayList<Order> getAllOrders(){
		ArrayList<Order> order = new ArrayList<Order>();
		try {
			order = new DB_Order(this.getConnection()).getAll();
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return order;
	}
	
	public Order getOrder(int id) throws IdNotFoundException{
		Order order = null;
		try {
			order = new DB_Order(getConnection()).getById(id);
		} catch (NoDBConnException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConnection();
		}
		return order;
	}
}
